package com.xingxuecn.limit.dao.impl;

import com.xingxuecn.limit.common.model.Page;
import com.xingxuecn.limit.common.model.Role;
import com.xingxuecn.limit.common.utils.DbUtils;
import com.xingxuecn.limit.dao.RoleDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author 你的名字 at 2018/9/10 15:22
 */
public class RoleDaoImpl implements RoleDao {
    private QueryRunner qr = new QueryRunner();

    @Override
    public List<Role> findAll() throws SQLException {
        String sql = "select * from role ";
        Connection connection = DbUtils.getConnection();
        List<Role> result;
        try {
            result = qr.query(connection, sql, new BeanListHandler<Role>(Role.class));
        } finally {
            connection.close();
        }
        return result;
    }

    @Override
    public List<Role> search(Role role, Integer... limit) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from role where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (role.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(role.getId());
        }
        if (role.getName() != null) {
            sql.append(" and name like ?");
            jdbcParams.add("%" + role.getName() + "%");
        }
        if (role.getState() != null) {
            sql.append(" and state = ?");
            jdbcParams.add(role.getState());
        }
        if (role.getType() != null) {
            sql.append(" and type = ?");
            jdbcParams.add(role.getType());
        }



        if (limit != null && limit.length > 0) {
            if (limit.length == 1) {
                sql.append(" limit ?");
                jdbcParams.add(limit[0]);
            } else if (limit.length == 2) {
                sql.append(" limit ?,?");
                jdbcParams.add(limit[0]);
                jdbcParams.add(limit[1]);
            } else {
                throw new RuntimeException("你的参数传多了，limit最大只能2个");
            }
        }
        Connection connection = DbUtils.getConnection();
        List<Role> result;
        try {
            result = qr.query(connection, sql.toString(), new BeanListHandler<Role>(Role.class), jdbcParams.toArray());
        } finally {
            connection.close();
        }
        return result;
    }

    @Override
    public int add(Role role) throws SQLException {
        String sql = "insert into role(id,name,state,type) values(?,?,?,?)";
        BigInteger id = null;
        Connection connection = DbUtils.getConnection();
        try {
            id = qr.insert(connection, sql, new ScalarHandler<BigInteger>(), new Object[]{
                    role.getId(),
                    role.getName(),
                    role.getState(),
                    role.getType(),
            });
        } finally {
            connection.close();
        }
        if (id != null) {
            role.setId(id.intValue());
        }

        return 1;
    }

    @Override
    public int update(Role role) throws SQLException {
        String sql = "update role set name =?,state=? ,type=? where id = ?";
        int rows;
        Connection connection = DbUtils.getConnection();
        try {
            rows = qr.update(connection, sql, new Object[]{

                    role.getName(),
                    role.getState(),
                    role.getType(),
                    role.getId()
            });
        } finally {
            connection.close();
        }
        return rows;
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from role where id = ?";
        int rows;
        Connection connection = DbUtils.getConnection();
        try {
            rows = qr.execute(connection, sql, new Object[]{id});
        } finally {
            connection.close();
        }
        return rows;
    }


    @Override
    public int count(Role role) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from role where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (role.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(role.getId());
        }
        if (role.getName() != null) {
            sql.append(" and name like ?");
            jdbcParams.add("%" + role.getName() + "%");
        }
        if (role.getState() != null) {
            sql.append(" and state = ?");
            jdbcParams.add(role.getType());
        }
        if (role.getType() != null) {
            sql.append(" and type = ?");
            jdbcParams.add(role.getType());
        }
        Connection connection = DbUtils.getConnection();
        Long result;
        try {
            result = qr.query(connection, sql.toString(), new ScalarHandler<Long>(), jdbcParams.toArray());
        } finally {
            connection.close();
        }
        if (result != null) {
            return result.intValue();
        }
        return 0;
    }

    @Override
    public Page searchPage(Role role, Integer page, Integer rows) throws SQLException {
        int total = this.count(role);
        List<Role> roleList = this. search(role, (page - 1) * rows, rows);
        return new Page(total,roleList);
    }
}






